Друзья, всем привет. Сегодня хотелось бы поговорить про непонятную и загадочную функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Думаю, что каждый, кто хоть как-то работает со сводными таблицами, сталкивался с ней. Как обычно, я не расскажу ничего нового. Всё это уже есть так или иначе на просторах интернета. Я лишь поделюсь своим опытом и тем, как мне в своё время это функция очень помогла. Поехали.
Ссылка на файл (с уже прописанными формулами и одним листом, где можете попробовать прописать самостоятельно) - https://disk.yandex.ru/i/XJNiy7WI2rrMqQ
Обычно первое знакомство происходит примерно так. Вы построили сводную таблицу, потом возникает необходимость сослаться на какую-нибудь ячейку внутри этой сводной, но вместо любимых и ламповых ссылок типа В4 мы получаем вот это:
Ступор, небольшое замешательство, осознание, что чего-то пошло не так. Самые смелые заканчивают ввод формулы, видят нормальное значение, радуются, копируют формулу, после чего радость заканчивается. Потом, скорее всего, поиски в интернете "как избавиться от ПОЛУЧИТЬ.ДАННЫЕ...". Про то, как избавиться, мы ещё поговорим в самом конце. А пока...
В чём сила этой функции, брат?
Если вы будете смотреть различные видео по этой функции, почти во всех из них будут упоминаться диаграммы. Спорить не буду, у сводных диаграмм есть некоторые ограничения, но я, если честно, решил этот момент пропустить, так как разными путями эти ограничения можно обойти (если конкретно вам эта функция помогает именно при построении диаграмм, напишите в комментариях). Речь же пойдёт про другое, а именно, про перенос данных из сводной таблицы в обычный отчёт (таблицу). Вот тут, как мне кажется, функция раскрывает весь свой потенциал. Есть у нас данные, мы построили на основе этих данных сводную:
Предположим, что таблица постепенно наполняется. То есть сейчас есть данные до ноября, но потом будут и за декабрь. Источник лучше преобразовать в "умную" таблицу, так потом будет чуть проще обновлять сводную. Данные эти нам потом необходимо перенести в отчёт установленной формы:
Давайте сейчас определим сложности, с которыми мы столкнёмся, и которые не позволят нам банальным копированием-вставкой или прямой ссылкой заполнить наш отчёт:
Клиенты продавали не все наименования. Но, в теории, всё это у них может быть.
Порядок клиентов в сводной таблице и в отчёте разный ("потому что" ©).
Отчёт сразу за год, а у нас пока данные только до ноября. Добавлять формулы потом отдельно на декабрь не очень хочется. Хочется в начале года прописать формулу, потом сводную обновлять и радоваться жизни.
Почему не СУММЕСЛИМН? Потому что структура отчёта не позволяет суммировать продажи по наименованиям внутри каждого клиента. Или городить какую-нибудь формулу массива (не пробовал, но может и получится).
Можно ещё немного пофантазировать и предположить, что могут появиться новые клиенты и новые наименования. И решение должно это учитывать (добавил клиента с наименованиями, скопировал формулу, гордый собой пошёл пить чай/кофе).
И вот тут как раз на сцену выходит она - ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GetPivotData).
Не смотря на всю свою несуразность и загадочность, начиная с названия и заканчивая синтаксисом, функция довольно простая:
Первый аргумент - поле из сводной, по которому мы производим вычисления. Далее - ЛЮБАЯ ячейка из сводной таблицы (обычно берут верхнюю левую). А потом идут пары: в каком поле что нужно найти.
Первоначально нужные элементы указываются в виде текста. Вот тут и начинается самое интересное. Ведь вместо текста можно указывать ссылки на ячейки (учитывая все закрепления, конечно же). То есть если возвращаться к нашему отчёту, формулу для наименований можно прописать следующим образом (показана формула для ячейки В3):
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма, руб";
'Исходник 2'!$G$1;"Наименование";$A3;"Клиент";$A$2;"Месяцы (Дата)";B$1)
Исходник 2 - лист, на котором находится сама сводная таблица.
В ячейке хотим видеть значение из поля "Сумма, руб" нашей сводной. G1 - ссылка на ячейку сводной. А дальше те самые пары. В поле "Наименование" ищем значение из ячейки А3 (1), в "Клиент" ищем А2 (2), в "Месяцы(даты)" ищем В1 (3). Порядок пар роли не играет.
ВАЖНО! Названия полей (наименование, клиент, месяцы(даты)), тоже можно сделать в виде ссылок на ячейки, если таковые есть. А вот название поля данных, в котором происходит расчёт (сумма, руб) обязательно должно быть указано текстом. В любом случае, названия должны совпадать с теми, которые указаны в сводной таблице.
Если сейчас скопировать формулу на все месяцы, то в декабре будет ошибка #ССЫЛКА, так как нет такого элемента в сводной таблице. Чтобы убрать это непотребство, просто добавляем функцию ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма, руб";
'Исходник 2'!$G$1;"Наименование";$A3;"Клиент";$A$2;"Месяцы (Дата)";B$1);"")
Да, решение далеко от идеального. Хотя бы потому, что для клиентов придётся писать свою формулу, а для наименований свою. Скопировать всё разом на всю таблицу тоже не получится, потому что нужно будет менять ссылку с названием клиента. Но это всё делается за минуту. А преимущества такого подхода очевидны:
Если в нашей сводной что-то будет смещено, то формула не сломается, в отличие от прямой ссылки.
Когда будут данные за декабрь, мы их добавляем в нашу "умную" таблицу, обновляем сводную и готово.
Если появится новый клиент или наименование, то нужно будет добавить новые строки в отчёт и скопировать формулу.
Недостатки у такого подхода, конечно, тоже имеются:
Если фильтровать сводную или полностью перелопатить её структуру, то значения в формуле будут пересчитываться в соответствии с тем, что сейчас отображается в сводной. Таким образом, например, если поставим фильтр в сводной таблице на какие-то определённые наименования, а потом забудем фильтр очистить, то в отчёте суммы по этим наименованиям потеряем (ячейки будет пустыми, ошибок не будет, ведь мы приправили всё ЕСЛИОШИБКОЙ).
Если отчёт и сводная находятся в разных книгах, то формула будет работать только тогда, когда обе книги открыты одновременно (не относится к ситуации, когда обе открыли, а потом книгу со сводной закрыли).
Заключение.
На этом, пожалуй, всё. Повторюсь ещё раз, я описал свой опыт. С моей субъективной колокольни, именно такое использование данной функции наиболее полезное и практичное. То, что потом на основе таких вот отчётов можно строить обычные диаграммы - это уже производное от главного, а именно от того, что основное преимущество данной функции - более менее безопасный и гибкий способ переносить данные из сводной таблицы в обычные таблицы.
Как обычно, спасибо всем, кто потратил своё драгоценное время на чтение данной статьи. Надеюсь, было полезно. И да, если уже пользуетесь этой функцией, поделитесь в комментариях, какие задачи она вам помогает решать. Наверняка есть что-то такое, чего я о ней не знаю (никогда не было стыдно признаться в том, что я чего-то не знаю). Или если при использовании данной функции вы сталкивались с какими-то критичными трудностями. Да пребудет с нами сила ИКСэль.
P.S.
Алё, а отключить-то как?! На всякий случай напишу, как отключить автоматическое создание этой функции, когда ссылаемся на ячейки сводной (большинству она вот совсем не нужна). Всё просто:
Выбираем любую ячейку сводной.
Вкладка Анализ - группа Сводная таблица - раскрываем Параметры - снимаем галочку Создать GetPivotData.
Profit!!11
Больше вас это богомерзкое чудовище не побеспокоит. А если нужна будет, можете вводить её как обычную функцию.
